package com.situ.student.dao.impl;

import com.situ.student.common.Global;
import com.situ.student.dao.StudentDao;
import com.situ.student.model.Student;
import com.situ.student.model.search.StudentSearchBean;
import com.situ.student.util.Pagination;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;

//数据仓库
public class StudentDaoImpl implements StudentDao {
    //行映射器
    private final RowMapper<Student> rowMapper = new BeanPropertyRowMapper<Student>(Student.class);

    @Override
    public List<Student> findAll(Pagination pagination, StudentSearchBean ssb) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        List<Object> args = new ArrayList<>();//查询参数

        String select = "select id,stu_id,name,sex,birthday,pinyin,phone,email,qq,wechat from t_student";
        StringBuilder where = new StringBuilder();
        where.append(" where 1=1");
        if (StringUtils.hasText(ssb.getStuId())) {
            where.append(" and stu_id = ?");
            args.add(ssb.getStuId());
        }
        if (StringUtils.hasText(ssb.getName())) {
            where.append(" and name like ?");
            args.add("%" + ssb.getName() + "%");
        }
        if (StringUtils.hasText(ssb.getSex())) {
            where.append(" and sex = ?");
            args.add(ssb.getSex());
        }
        if (StringUtils.hasText(ssb.getPhone())) {
            where.append(" and phone like ?");
            args.add("%" + ssb.getPhone() + "%");
        }
        if (ssb.getBirthdayStart() != null) {
            where.append(" and birthday >= ?");
            args.add(ssb.getBirthdayStart());
        }
        if (ssb.getBirthdayEnd() != null) {
            where.append(" and birthday < ?");
            args.add(ssb.getBirthdayEnd());
        }

        //查询记录总数
        String countSql = "select count(0) from (" + (select + where) + ") t";
        //记录总数
        Long count = jdbcTemplate.queryForObject(countSql, Long.class, args.toArray());
        pagination.setTotal(count);//设置总记录数

        //分页查询sql
        String limitSql = select + where + " limit ?,?";
        args.add(pagination.getOffset());
        args.add(pagination.getLimit());

        //执行查询操作
        List<Student> students = jdbcTemplate.query(limitSql, rowMapper, args.toArray());
        return students;
    }

    @Override
    public int deleteByIds(List<Integer> ids) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        if (ids == null || ids.isEmpty()) {
            return -1;
        }
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < ids.size(); i++) {
            sb.append("?,");
        }
        String ph = sb.substring(0, sb.length() - 1);

        String sql = "delete from t_student where id in (" + ph + ")";

        int rows = jdbcTemplate.update(sql, ids.toArray());

        return rows;
    }

    @Override
    public int save(Student stu) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        String sql = "insert into t_student (stu_id,name,pinyin,sex,birthday,phone,wechat) values (?,?,?,?,?,?,?)";

        //写操作
        int count = jdbcTemplate.update(sql, stu.getStuId(), stu.getName(), stu.getPinyin(), stu.getSex()
                , stu.getBirthday(), stu.getPhone(), stu.getWechat());

        return count;
    }

    @Override
    public int update(Student stu) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        String sql = "update t_student set stu_id=?,name=?,pinyin=?,sex=?,birthday=?,phone=?,wechat=? where id=?";

        //写操作
        int count = jdbcTemplate.update(sql, stu.getStuId(), stu.getName(), stu.getPinyin(), stu.getSex()
                , stu.getBirthday(), stu.getPhone(), stu.getWechat(), stu.getId());

        return count;
    }

    @Override
    public Student findById(Integer id) {
        JdbcTemplate jdbcTemplate = Global.getJdbcTemplate();
        String sql = "select id,stu_id,name,sex,birthday,pinyin,phone,email,qq,wechat from t_student where id = ?";

        Student stu = jdbcTemplate.queryForObject(sql, rowMapper, id);

        return stu;
    }
}
